from dateutil import parser
from datetime import datetime 
from openpyxl.styles import Alignment
from . import myexcel
from config import *
from sql import sqlseverDB
import openpyxl,re,time,math,os,requests,json


def create_dj2(fpath:str):
    
    if fpath.endswith('.xlsx'):


        sheet_datas=myexcel.getDictDatasByEecel2(fpath)

        datas=[]

        for r in sheet_datas:

            datas.append([1,r])
  


        nwb=openpyxl.Workbook()
        nws=nwb[nwb.sheetnames[0]]
        nws.append(['关键词','最低价格','最高价格','店铺ID','产品名字','产品链接'])
        xunhcot=0
        while True:
            if len(datas)==0:
                break
            ndatas=[]
            for dj,rdata in datas:
                mbdada=['' for col in range(nws.max_column)]
                mbdada[0]=rdata['关键词']
                mbdada[1]=rdata.get('最低价')
                mbdada[2]=rdata.get('最低价')
                mbdada[3]=rdata['商家名字']
                mbdada[4]=''
                mbdada[5]=rdata['产品链接']

                if dj>0:
                    nws.append(mbdada)

                if dj-1>0:
                    ndatas.append([dj-1,rdata])
            
            datas=ndatas
            xunhcot+=1
            print(f'第{xunhcot}圈生成,剩余:{len(datas)}')
        time_local = time.localtime(time.time())
        dt = time.strftime("%Y-%m-%d", time_local)
        npath=f'点击任务_卡价格_{dt}.xlsx'
        nwb.save(npath)
        print(f'生成点击表完毕《{npath}》')

def getTimeStr():

    time_local = time.localtime(time.time())
    dt = time.strftime("%Y-%m-%d_%H-%M-%S", time_local)
    return dt

def getTimeStr2(ct):

    time_local = time.localtime(ct)
    dt = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
    return dt

def getTimeTit():

    time_local = time.localtime(time.time())
    dt = time.strftime("%Y-%m-%d_%H-%M-%S", time_local)
    return dt

def trydosql(cms,sqlstr,params=None,type=1,force=0):

    while True:
        try:
            if type==1:
                res=cms.ExecQuery(sqlstr,params)
            elif type==2:
                res=cms.ExecNoQuery(sqlstr,params)
                if not res and force==1:
                    time.sleep(1)
                    continue
            elif type==3:
                res=cms.ExecQuerydict(sqlstr,params)
            
            return res
        except Exception as e:
            pass
        time.sleep(1)

def sqlToExcel(fnameqz,cms,sqlstr,params=None,dbpmap=None):
    rs=trydosql(cms,sqlstr,params,3)

    wb=openpyxl.Workbook()
    ws=wb[wb.sheetnames[0]]
    if not dbpmap:
        ws.append(list(rs[0].keys()))
        for r in rs:
            ws.append(list(r.values()))

        for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
            for cell in row:
                cell.alignment = Alignment(horizontal='left', vertical='center')
    else:
        curks=list(dbpmap.keys())
        ws.append(curks)
        ws.append(list(dbpmap.values()))

        for r in rs:
            cur_line=[]
            for ys_k in curks:
                cur_line.append(r[ys_k])
            ws.append(cur_line)
            
        for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
            for cell in row:
                cell.alignment = Alignment(horizontal='left', vertical='center')

    current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
    fname=f'{fnameqz}_{current_time_str}.xlsx'
    wb.save(fname)
    return fname

def crate_excel(fnameqz,rs,dbpmap=None):

    if len(rs)>0:
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        if not dbpmap:
            ws.append(list(rs[0].keys()))
            for r in rs:
                ws.append(list(r.values()))
            for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
                for cell in row:
                    cell.alignment = Alignment(horizontal='left', vertical='center')

        else:
            curks=list(rs[0].keys())
            ws.append(curks)
            ws.append([dbpmap.get(kkk) if dbpmap.get(kkk) else kkk  for kkk in curks])

            for r in rs:
                ws.append(list(r.values()))
                
            for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
                for cell in row:
                    cell.alignment = Alignment(horizontal='left', vertical='center')

        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
        fname=f'{fnameqz}_{current_time_str}.xlsx'
        wb.save(fname)
        return fname
    else:
        return 0


def convert_to_timestamp(time_str):
    # 使用 dateutil.parser 解析时间字符串
    try:
        dt = parser.parse(time_str)
        
        # 转换为时间戳
        timestamp = time.mktime(dt.timetuple())

        return int(timestamp)
    except Exception as e:
        return -1

def isContain(kw,tit):

    glwords=[re.escape(item) for item in kw.split()]
    flag0=True
    if tit and isinstance(tit,str):
        for gkw in glwords:
            if not re.search(r'\b{}\b'.format(gkw),tit,re.I):
                flag0=False
                break
        return flag0
    
    else:
        return False
    
ms=sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)

smtys={
    '标题':'yntitle',
    '产品ID':'itemid',
    '详细参数':'attributes',
    'SKU图片网址':'skuImg',
    '价格':'skuPirce',
    'SKUID':'skuid',
    '描述':'pdec'
}

zjys={
    'Option for Variation 1':'sku1',
    'Option for Variation 2':'sku2',
}

def getsmtpro(params,everycount,filecount):

    print(f'正在获取模板 {params[0]} 的数据...')
    
    #现获取符合条件的itemid
    sqlstr0='select min(daochu) from ProShopeePh'

    sqlstr=f''' select * from ProShopeePh where itemid in
            (SELECT TOP {everycount*filecount} itemid FROM ProShopeePh
            where pmbid=? and rating_star>=? and skuSold>=? and historical_sold>=? 
            and cmt_count>=? and (skuPirce between ? and ?) and ExportConditionMet=1
            and daochu=? GROUP BY itemid order by MAX(daochuTime))'''
    while True:
        try:
            rp=ms.ExecQuerydict(sqlstr,params)
            break
        except Exception as e:
            pass
    

    pids=[]
    
    for item in rp:
        for skuitem in ['sku1','sku2']:
            if item[skuitem]:
                if len(item[skuitem])>20:
                    item[skuitem]=(item[skuitem])[:18]
        if not item['pdec']:
            item['pdec']=item['yntitle']

        if item['pdec']:
            if len(item['pdec'])>2500:
                item['pdec']=(item['pdec'])[:2500]
        
        pids.append(item['itemid'])
    
    count_dict = {}
    for item in rp:
        item_id = item['itemid']
        sku1 = item['sku1']
        sku2 = item['sku2']
        if sku1 and not sku2:
            if (item_id, sku1) in count_dict:
                count_dict[(item_id, sku1)] += 1
                item['sku1'] = f"{sku1}_{count_dict[(item_id, sku1)]}"
            else:
                count_dict[(item_id, sku1)] = 1

        if sku2:
            if (item_id,sku1,sku2) in count_dict:
                item['sku2'] = f"{sku2}_{count_dict[(item_id,sku1,sku2)]}"
                count_dict[(item_id,sku1,sku2)] += 1
            else:
                count_dict[(item_id,sku1,sku2)] = 1
    pids=list(set(pids))
    aff=0
    daochutime=int(time.time())
    for j in range(math.ceil(len(pids)/1000)):
        cpids=pids[j*0:(j+1)*1000]
        wstr=','.join(['?' for _ in cpids])
        aff+=ms.ExecNoQuery(f'update ProShopeePh set daochu=daochu+1,daochuTime={daochutime} where [itemid] in ({wstr})',cpids)

    print(f'mbid:{params[0]},已将{len(pids)}个产品daochu+1,daochuTime:{daochutime}')
    
    rp_dict={}

    for r in rp:
        itemid = r['itemid']
        if rp_dict.get(itemid):
            rp_dict[itemid].append(r)
        
        else:
            rp_dict[itemid]=[r]
    
    return list(rp_dict.values())

def dc_exceldata(pros,mbid,everycount,filecount,phl,pbs,dcmr):

    #print(f'导出份数:{filecount},每份最少量:{everycount}')
    rmb=ms.ExecQuery('select dfile from [mb] where did=?',(mbid,))
    jdpath=rmb[0][0]
    jdpaths=jdpath.split('\\')
    if not os.path.exists('官方模版表'):
        os.mkdir('官方模版表')

    mbpath=f'官方模版表/{jdpaths[-1]}'

    downpath=f'http://198.12.127.43/{jdpaths[-2]}/{jdpaths[-1]}'
    print(f'正在从服务器下载模版id:{mbid} 文件...')
    while True:
        try:
            cot=requests.get(downpath).content
            break
        except Exception as e:
            pass
    with open(mbpath,'wb') as f:
        f.write(cot)
    while True:
        try:
            rys=ms.ExecQuery('select * from lb where ldid=? order by lrow',(mbid,))
            break
        except Exception as e:
            pass
    xhs=math.ceil(len(pros)/everycount)

    for j in range(xhs):
        wb=openpyxl.load_workbook(mbpath)
        ws=wb['Template']
        row=7
        lowind=j*everycount
        maxind=(j+1)*everycount if (j+1)*everycount<len(pros) else len(pros)
        curprocount=maxind-lowind
        for item in pros[lowind:maxind]:
            for pro in item:
                allimgs=[pro['image']]
                if pro['images']:
                    allimgs.extend(json.loads(pro['images']))
                pro['price']=int(phl*pro['price']*pbs)
                pro['skuPirce']=int(phl*pro['skuPirce']*pbs)
                rol=1
                try:
                    for ry in rys:
                        mbcol=ry[1]
                        smtcol=ry[2]
                        gd=ry[4]
                        if '无' not in smtcol and smtcol:
                            if smtcol=='SKU名称':
                                if zjys.get(mbcol):
                                    ws.cell(row=row,column=rol).value=pro[zjys.get(mbcol)]
                                    if zjys.get(mbcol)=='sku1' and not pro['sku1']:
                                        ws.cell(row=row,column=rol).value='style'

                            elif smtcol=='主图网址':
                                if len(allimgs)>0:
                                    ws.cell(row=row,column=rol).value=allimgs.pop(0)
                            elif smtcol=='SKU图片网址':
                                if not pro['skuImg']:
                                    ws.cell(row=row,column=rol).value=pro['image']
                                else:
                                    ws.cell(row=row,column=rol).value=pro['skuImg']
                            else:
                                ws.cell(row=row,column=rol).value=str(pro[smtys.get(smtcol)])
                        
                        else:
                            ws.cell(row=row,column=rol).value=gd
                            if gd=='size' and  not pro['sku2']:
                                ws.cell(row=row,column=rol).value=''

                        rol+=1
                    row+=1
                except Exception as e:
                    pass

        wb.save(f'{dcmr}/{mbid}-{curprocount}-{j+1}.xlsx')
        print(f'成功保存：{mbid}-{curprocount}-{j+1}.xlsx')
        if j+1==filecount:
            break

def getpro2():
    #现获取符合条件的itemid

    print('正在链接数据库...')
    sqlstr='''
            select * from ProShopeePh where
            daochu>0 and daochu2=0 and isqq=0
            and yndes is not null 
            and newmaxprice is not null
            and pmbid is not null
            '''

    rp=trydosql(ms,sqlstr,type=3)

    pids=[]
    
    for item in rp:
        for skuitem in ['sku1','sku2']:
            if item[skuitem]:
                if len(item[skuitem])>20:
                    item[skuitem]=(item[skuitem])[:18]

        if not item['yndes']:
            item['yndes']=item['yntitle']

        if item['yndes']:
            if len(item['yndes'])>2500:
                item['yndes']=(item['yndes'])[:2500]
        pids.append(item['itemid'])
    
    count_dict = {}
    for item in rp:
        item_id = item['itemid']
        sku1 = item['sku1']
        sku2 = item['sku2']
        if sku1 and not sku2:
            if (item_id, sku1) in count_dict:
                count_dict[(item_id, sku1)] += 1
                item['sku1'] = f"{sku1}_{count_dict[(item_id, sku1)]}"
            else:
                count_dict[(item_id, sku1)] = 1

        if sku2:
            if (item_id,sku1,sku2) in count_dict:
                item['sku2'] = f"{sku2}_{count_dict[(item_id,sku1,sku2)]}"
                count_dict[(item_id,sku1,sku2)] += 1
            else:
                count_dict[(item_id,sku1,sku2)] = 1
    dctime=int(time.time())
    pids=list(set(pids))
    aff=0
    for j in range(math.ceil(len(pids)/1000)):
        cpids=pids[j*0:(j+1)*1000]
        wstr=','.join(['?' for _ in cpids])

        aff+=ms.ExecNoQuery(f'update ProShopeePh set daochu2=1,daochuTime2={dctime} where [itemid] in ({wstr})',cpids)
    print(f'已将{aff}个sku设置为导出状态置1,导出时间:{getTimeStr2(dctime)}')
    
    rp_dict={}

    for r in rp:
        mbid = r['pmbid']
        if rp_dict.get(mbid):
            rp_dict[mbid].append(r)
        
        else:
            rp_dict[mbid]=[r]
    
    for mbid,pros in rp_dict.items():
        rp2_dict={}
        
        for pro in pros:
            pro_itemid=pro['itemid']
            if rp2_dict.get(pro_itemid):
                rp2_dict[pro_itemid].append(pro)
            else:
                rp2_dict[pro_itemid]=[pro]
        
        rp_dict[mbid]=rp2_dict
        

    print(f'成功获取 {len(pids)} 个 可二次铺货的产品')

    return rp_dict

def rcpro(sjbm,dcCount):
    sqlstr=f'''
        select * from proshopeeph where itemid in(
            select top {dcCount} itemid from ProShopeePh 
                where isqq=1 and zlorder2=1 and daochu3=0 
                group by itemid 
                order by max(totalBuyers2) desc
        )
        '''
    rp=trydosql(ms,sqlstr,type=3)
    pids=[]
    for item in rp:
        for skuitem in ['sku1','sku2']:
            if item[skuitem]:
                if len(item[skuitem])>20:
                    item[skuitem]=(item[skuitem])[:18]

        if not item['yndes']:
            item['yndes']=item['yntitle']

        if item['yndes']:
            if len(item['yndes'])>2500:
                item['yndes']=(item['yndes'])[:2500]
        pids.append(item['itemid'])
    
    
    count_dict = {}
    for item in rp:
        item_id = item['itemid']
        sku1 = item['sku1']
        sku2 = item['sku2']
        if sku1 and not sku2:
            if (item_id, sku1) in count_dict:
                count_dict[(item_id, sku1)] += 1
                item['sku1'] = f"{sku1}_{count_dict[(item_id, sku1)]}"
            else:
                count_dict[(item_id, sku1)] = 1

        if sku2:
            if (item_id,sku1,sku2) in count_dict:
                item['sku2'] = f"{sku2}_{count_dict[(item_id,sku1,sku2)]}"
                count_dict[(item_id,sku1,sku2)] += 1
            else:
                count_dict[(item_id,sku1,sku2)] = 1

    pids=list(set(pids))
    print(f'成功获取 {len(pids)} 个 不侵权入仓的产品')
    aff=0
    dctime=int(time.time())
    for j in range(math.ceil(len(pids)/1000)):
        cpids=pids[j*0:(j+1)*1000]
        wstr=','.join(['?' for _ in cpids])
        aff+=ms.ExecNoQuery(f'''update ProShopeePh 
                                    set 
                                        daochu3=1,
                                        isCjRcUrl=1,
                                        daochuTime3={dctime},
                                        sjbm = CASE WHEN sjbm IS NULL THEN ? ELSE sjbm END 
                                    where [itemid] in ({wstr})''',[sjbm]+cpids)


    print(f'已将{aff}个sku,daochu3和iscjrcurl的状态置1,商家编码更新为:{sjbm},导出时间:{getTimeStr2(dctime)}')
    
    rp_dict={}

    for r in rp:
        mbid = r['pmbid']
        if rp_dict.get(mbid):
            rp_dict[mbid].append(r)
        
        else:
            rp_dict[mbid]=[r]
    
    for mbid,pros in rp_dict.items():
        rp2_dict={}
        
        for pro in pros:
            pro_itemid=pro['itemid']
            if rp2_dict.get(pro_itemid):
                rp2_dict[pro_itemid].append(pro)
            else:
                rp2_dict[pro_itemid]=[pro]
        
        rp_dict[mbid]=rp2_dict
        


    return rp_dict

def getpro3(rp,sjbm):
    #现获取符合条件的itemid

    pids=[]
    pids=list(set(pids))
    for item in rp:
        for skuitem in ['sku1','sku2']:
            if item[skuitem]:
                if len(item[skuitem])>20:
                    item[skuitem]=(item[skuitem])[:18]
        if not item['yndes']:
            item['yndes']=item['yntitle']
        if item['yndes']:
            if len(item['yndes'])>2500:
                item['yndes']=(item['yndes'])[:2500]
        pids.append(item['itemid'])
    
    count_dict = {}
    for item in rp:
        item_id = item['itemid']
        sku1 = item['sku1']
        sku2 = item['sku2']
        if sku1 and not sku2:
            if (item_id, sku1) in count_dict:
                count_dict[(item_id, sku1)] += 1
                item['sku1'] = f"{sku1}_{count_dict[(item_id, sku1)]}"
            else:
                count_dict[(item_id, sku1)] = 1

        if sku2:
            if (item_id,sku1,sku2) in count_dict:
                item['sku2'] = f"{sku2}_{count_dict[(item_id,sku1,sku2)]}"
                count_dict[(item_id,sku1,sku2)] += 1
            else:
                count_dict[(item_id,sku1,sku2)] = 1

    pids=list(set(pids))
    print(f'成功获取 {len(pids)} 个 入仓数据的产品')
    aff=0
    dctime=int(time.time())
    for j in range(math.ceil(len(pids)/1000)):
        cpids=pids[j*0:(j+1)*1000]
        wstr=','.join(['?' for _ in cpids])
        aff+=ms.ExecNoQuery(f'''update ProShopeePh 
                                    set 
                                        daochu3=1,
                                        isCjRcUrl=1,
                                        daochuTime3={dctime},
                                        sjbm = CASE WHEN sjbm IS NULL THEN ? ELSE sjbm END 
                                    where [itemid] in ({wstr})''',[sjbm]+cpids)


    print(f'已将{aff}个sku,daochu3和iscjrcurl的状态置1,商家编码更新为:{sjbm},导出时间:{getTimeStr2(dctime)}')
    
    rp_dict={}

    for r in rp:
        mbid = r['pmbid']
        if rp_dict.get(mbid):
            rp_dict[mbid].append(r)
        
        else:
            rp_dict[mbid]=[r]
    
    for mbid,pros in rp_dict.items():
        rp2_dict={}
        
        for pro in pros:
            pro_itemid=pro['itemid']
            if rp2_dict.get(pro_itemid):
                rp2_dict[pro_itemid].append(pro)
            else:
                rp2_dict[pro_itemid]=[pro]
        
        rp_dict[mbid]=rp2_dict
        

    return rp_dict

def dc_exceldata2(pro_dict,status=0):
    current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
    dcmr=f'二次铺货导出结果{current_time_str}'
    if status==1:
        dcmr=f'不侵权可入仓导出结果{current_time_str}'
    elif status==2:
        dcmr=f'侵权可入仓导出结果{current_time_str}'
    os.mkdir(dcmr)
    mbcount=len(pro_dict.keys())
    print(f'模版分布:{list(pro_dict.keys())}\n共{mbcount} 个模版')
    j=0
    for mbid,rp2_dict in pro_dict.items():
        rmb=ms.ExecQuery('select dfile from [mb] where did=?',(mbid,))
        jdpath=rmb[0][0]
        jdpaths=jdpath.split('\\')
        if not os.path.exists('官方模版表'):
            os.mkdir('官方模版表')
        mbpath=f'官方模版表/{jdpaths[-1]}'

        while True:
            try:
                downpath=f'http://198.12.127.43/{jdpaths[-2]}/{jdpaths[-1]}'
                print(f'正在从服务器下载模版id:{mbid} 文件...')
                while True:
                    try:
                        cot=requests.get(downpath).content
                        break
                    except Exception as e:
                        pass
                with open(mbpath,'wb') as f:
                    f.write(cot)
                rys=ms.ExecQuery('select * from lb where ldid=? order by lrow',(mbid,))
                break
            
            except Exception as e:
                print(e)

        for itemid,pros in rp2_dict.items():
            
            wb=openpyxl.load_workbook(mbpath)
            ws=wb['Template']
            row=7
            maxskupricepro=max(pros,key=lambda item:item['skuPirce'])
            maxskuprice=maxskupricepro['skuPirce']
            #print(f"itemid:{itemid},maxskuprice:{maxskuprice}")
            for pro in pros:
                allimgs=[pro['image']]
                if pro['images']:
                    allimgs.extend(json.loads(pro['images']))
                
                pro['skuPirce']=int(pro['skuPirce']*pro['newmaxprice']/maxskuprice)
                rol=1
                try:
                    for ry in rys:
                        mbcol=ry[1]
                        smtcol=ry[2]
                        gd=ry[4]
                        if '无' not in smtcol and smtcol:
                            if smtcol=='SKU名称':
                                if zjys.get(mbcol):
                                    ws.cell(row=row,column=rol).value=pro[zjys.get(mbcol)]
                                    if zjys.get(mbcol)=='sku1' and not pro['sku1']:
                                        ws.cell(row=row,column=rol).value='style'

                            elif smtcol=='主图网址':
                                if len(allimgs)>0:
                                    ws.cell(row=row,column=rol).value=allimgs.pop(0)
                            elif smtcol=='SKU图片网址':
                                if not pro['skuImg']:
                                    ws.cell(row=row,column=rol).value=pro['image']
                                else:
                                    ws.cell(row=row,column=rol).value=pro['skuImg']
                            elif smtcol=='描述':
                                ws.cell(row=row,column=rol).value=pro['yndes']
                            else:
                                ws.cell(row=row,column=rol).value=str(pro[smtys.get(smtcol)])
                        
                        else:
                            ws.cell(row=row,column=rol).value=gd
                            if gd=='size' and  not pro['sku2']:
                                ws.cell(row=row,column=rol).value=''

                        rol+=1
                    row+=1
                except Exception as e:
                    pass
            print(f'模版id:{mbid},成功导出 1 个产品')

            wb.save(f'{dcmr}/模版-{mbid}-itemid-{itemid}.xlsx')
            print(f'成功保存：{dcmr}/模版-{mbid}-itemid-{itemid}.xlsx')
        j+=1
        print(f'剩余模版导出量 :{mbcount-j}')
        print()

def get_days_diff_from_timestamps(timestamp1, timestamp2):  
    """  
    使用datetime模块计算两个时间戳之间的天数差  
  
    参数:  
    timestamp1 (int): 第一个时间戳（秒为单位）  
    timestamp2 (int): 第二个时间戳（秒为单位）  
  
    返回:  
    int: 两个时间戳之间的天数差  
    """  
    # 将时间戳转换为datetime对象  
    dt1 = datetime.fromtimestamp(timestamp1)  
    dt2 = datetime.fromtimestamp(timestamp2)  
  
    # 计算时间差  
    delta = dt2 - dt1  
  
    # 提取天数差  
    days_diff = delta.days  
  
    return days_diff

def replace_first_occurrence(lst, old_value, new_value):  
    try:  
        index = lst.index(old_value)  
        lst[index] = new_value  
    except ValueError:  
        print(f"Value '{old_value}' not found in the list.")  
    return lst